# coding=utf-8
import copy
import os
import random
import re
import sys
import traceback

import openpyxl
import pandas
import pandas as pd
from loguru import logger
from openpyxl.reader.excel import load_workbook
from openpyxl.styles import Font

from models.stock_model import StockNumber, DayInfo
from mylib import download_all
from mylib.mycsv import sort_csv2
from send_email import send_email_xlsx
from update_sh import get_sh_down_date

import pandas as pd
import numpy as np


def add_multi_column_negative_ratio(input_file, output_file, target_columns=None):
    """
    为多个列分别计算小于0的行数占比，并在最后一行显示
    """
    # 读取Excel文件
    df = pd.read_excel(input_file)

    # 如果没有指定列，自动选择数值列
    if target_columns is None:
        target_columns = df.select_dtypes(include=[np.number]).columns.tolist()

    print(f"将计算以下列的负值占比: {target_columns}")

    # 为每个目标列计算负值占比
    ratio_results = {}
    for col in target_columns:
        if col in df.columns:
            # 计算该列小于0的行数占比
            negative_count = (df[col] < 0).sum()
            total_count = df[col].count()  # 非空值数量
            ratio = (negative_count / total_count * 100) if total_count > 0 else 0

            ratio_results[col] = {
                'negative_count': negative_count,
                'total_count': total_count,
                'ratio': ratio,
                'ratio_display': f"{ratio:.2f}%"
            }

            print(f"{col}: 负值数量={negative_count}, 总行数={total_count}, 占比={ratio:.2f}%")

    # 创建新行数据
    new_row_data = {}
    for col in df.columns:
        if col in target_columns:
            new_row_data[col] = ratio_results[col]['ratio_display']
        else:
            new_row_data[col] = "负值占比"

    # 添加新行到DataFrame
    new_row = pd.DataFrame([new_row_data])
    df_with_ratios = pd.concat([df, new_row], ignore_index=True)

    # 保存结果
    df_with_ratios.to_excel(output_file, index=False)
    print(f"处理完成！结果已保存到: {output_file}")

    return ratio_results


def get_3down_len(N, today_date, sn):
    """
    获取最近N次下跌，连续下跌天数计数
    """
    sc = f'stocks/{sn.ts_code}.csv'
    if not os.path.exists(sc):
        return None, None, None, None
    while not os.path.exists(sc):
        download_all.analysis_stock(sn)
    df = pandas.read_csv(sc)
    while str(df.iloc[0]['trade_date']) < str(today_date):
        download_all.analysis_stock(sn)
        df = pandas.read_csv(sc)
        if str(df.iloc[0]['trade_date']) >= str(today_date):
            logger.info(str(df.iloc[-1]['trade_date']).replace('-', ''))
            break
        else:
            logger.error(sn.name)
            return None, None, None, None
    today_d = None
    all_di_pct = []
    all_di_pct_list = []
    for row in df.index:
        d = DayInfo(sn, df.loc[row])
        if d.trade_date_str > today_date:
            continue
        if today_d is None:
            today_d = copy.deepcopy(d)
            if today_d.trade_date_str != today_date:
                return None, None, None, None
        all_di_pct.append(d)
        if len(all_di_pct) == N:
            all_di_pct_list = [float(dd.pct_chg) for dd in all_di_pct]
            break
    res = re.findall('stocks/(.*).csv', sc)
    link_code_arr = res[0].split('.')
    link_code = f'{link_code_arr[1]}{link_code_arr[0]}'
    hyperlink = f'"https://xueqiu.com/S/{link_code}"'
    date_arr_hyp = f'=HYPERLINK({hyperlink})'
    logger.info(f'{today_date}, {sn.name}, {hyperlink}')
    avg_pct = round(sum(all_di_pct_list) / N, 2)
    return today_d, avg_pct, date_arr_hyp


def get_all_stock_csv_path():
    for root, dirs, files in os.walk('stocks'):
        return [os.path.join(root, item) for item in files]


def run_down(stocks, cal_date, N, cal_len):
    today_date = str(cal_date[0])
    dname = os.path.basename(__file__).split('.')[0]
    log_dir = f'result_{dname}'
    if not os.path.exists(log_dir):
        os.makedirs(log_dir)
    full_path_csv = f'{log_dir}/{today_date}_a_pct{N}.csv'
    f_tk = open(full_path_csv, 'w', encoding='utf-8')
    cal_date_str = ','.join(cal_date[::-1])
    f_tk.write(f'连接,行业1,name1,{cal_date_str},行业,name,uptime,downtime,代码,当前价格,日期')
    df = pd.read_csv('cal_ops/all.csv')
    full_path_txt = f'{log_dir}/{today_date}_count1.txt'
    if os.path.exists(full_path_txt):
        os.remove(full_path_txt)
    logger.add(full_path_txt, format='{message}')
    cnt_number = 0
    for row in df.index:
        sn = StockNumber(df.loc[row])
        if 'ST' in sn.name:
            continue
        if '退' in sn.name:
            continue
        # if not str(sn.ts_code).startswith('30'):
        if not str(sn.ts_code).startswith('60') \
                and not str(sn.ts_code).startswith('30') \
                and not str(sn.ts_code).startswith('00'):
            continue
        if stocks and sn.name not in stocks:
            continue
        # if sn.industry not in [
        #     # '水力发电',
        #     # '小金属',
        #     # '煤炭开采',
        #     # '黄金',
        #     # '铜',
        # ]:
        #     continue
        try:
            """
                elf.name = sn.name
                self.ts_code = sn.ts_code
                self.industry = sn.industry
                self.trade_date = df_row_data['trade_date']
                self.open = df_row_data['open']
                self.high = df_row_data['high']
                self.low = df_row_data['low']
                self.close = float(df_row_data['close'])
                self.pre_close = df_row_data['pre_close']
                self.change = df_row_data['change']
                self.pct_chg = float(df_row_data['pct_chg'])
                self.vol = df_row_data['vol']
                self.amount = df_row_data['amount']
            """
            msg_arr = None
            abs_pct_arr = []
            downtime = 0
            uptime = 0
            for d in cal_date:
                today_d, avg_pct, date_arr_hyp = get_3down_len(
                    N, d, sn)
                if today_d is None:
                    abs_pct_arr.append('0')
                    continue
                abs_pct_arr.append(str(avg_pct))
                if msg_arr is None:
                    msg_arr = [today_d, avg_pct, date_arr_hyp]
            if msg_arr:
                idx2 = 0
                while 1:
                    if float(abs_pct_arr[idx2]) < float(0):
                        downtime += 1
                    elif downtime > 0:
                        break
                    else:
                        uptime += 1
                    idx2 += 1
                abs_pct_str = ','.join(abs_pct_arr[::-1])
                w_msg = f'\n{msg_arr[2]},{sn.industry},{sn.name},{abs_pct_str},{sn.industry},{sn.name},{uptime},{downtime},{sn.ts_code},{msg_arr[0].close},{today_date}'
                logger.info(f'{today_date}, {sn.name}, {w_msg}')
                f_tk.write(w_msg)
                f_tk.flush()
                cnt_number += 1
        except Exception as e:
            print(e, traceback.format_exc())

    if os.path.exists(full_path_txt):
        os.remove(full_path_txt)

    f_tk.close()
    save_path_csv = full_path_csv.replace('.csv', f'_sort.csv')
    sort_csv2(save_path_csv, full_path_csv, ['行业', 'uptime', 'downtime'], [True, False, False])
    if os.path.exists(full_path_csv):
        os.remove(full_path_csv)
    result_xlsx = csv2excel(save_path_csv)
    if os.path.exists(save_path_csv):
        os.remove(save_path_csv)
    # 使用示例2：指定特定列
    output_file = str(result_xlsx).replace('.xlsx', '_count.xlsx')
    add_multi_column_negative_ratio(
        result_xlsx,
        output_file,
        target_columns=cal_date[::-1]
    )
    color_excel_file_path = red_min_value(output_file, cal_len)
    if os.path.exists(output_file):
        os.remove(output_file)
    send_email_xlsx.send_xlsx(f'{cal_date[0]}_a_pct{N}', color_excel_file_path)


def red_min_value(excel_path, cal_len):
    wb = load_workbook(excel_path)
    sheet = wb['Sheet1']
    # 标记最小值的背景色为红色
    # 红色 FF0000
    # 绿色 00FF00
    # 白色 FFFFFF
    color_start_index = 4
    pre_indus = str('行业')
    color = 'FFFFFF'
    for row in sheet[2:sheet.max_row - 1]:
        for cell in row:
            if color_start_index < cell.col_idx < (color_start_index + cal_len):
                if float(cell.value) <= float(0):
                    cell.fill = openpyxl.styles.PatternFill(start_color='00FF00', end_color='00FF00', fill_type='solid')
                elif float(cell.value) > float(0):
                    cell.fill = openpyxl.styles.PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid')
                else:
                    cell.fill = openpyxl.styles.PatternFill(start_color='FFFFFF', end_color='FFFFFF', fill_type='solid')
            # elif cell.col_idx == (color_start_index + cal_len):
            #     # count1 黄色
            #     cell.fill = openpyxl.styles.PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
            if cell.col_idx >= (color_start_index + cal_len):
                # 按行业分颜色
                if (cell.col_idx == (color_start_index + cal_len)) and (str(cell.value) != pre_indus):
                    color = hex(random.randint(2 ** 23 + 4000000, 2 ** 24 - 1))[2:].zfill(6)
                    pre_indus = str(cell.value)
                cell.fill = openpyxl.styles.PatternFill(start_color=color, end_color=color, fill_type='solid')

    # 保存工作簿
    color_excel_file_path = excel_path.replace('.xlsx', '_color.xlsx')  # 加载Excel文件
    if os.path.exists(color_excel_file_path):
        os.remove(color_excel_file_path)
    sheet.freeze_panes = "D2"
    wb.save(color_excel_file_path)
    return color_excel_file_path


def csv2excel(full_path_csv):
    # csv转excel
    try:
        excel_path = str(full_path_csv).replace('.csv', '.xlsx')
        # 读取CSV文件
        df = pd.read_csv(full_path_csv)
        # df_unique = df.drop_duplicates()
        # df2 = df_unique.iloc[:, :5]
        # 将DataFrame写入Excel文件
        # df2.to_csv(full_path_csv)
        df.to_excel(excel_path, index=False)
        return excel_path
    except Exception as e:
        print(e)


def get_stocks(txt_path):
    with open(txt_path) as fr:
        stocks_names = [item.strip() for item in fr.readlines() if item.strip()]
        return list(set(stocks_names))


if __name__ == '__main__':
    # 计算离最近左右N日，计算当前价与顶点下跌幅度
    cal_len = 50
    N = 5
    sh_dict, sh_date = get_sh_down_date()

    start_bkd = 0
    try:
        arguments = sys.argv[1:]
        if arguments:
            start_bkd = int(arguments[0])
        else:
            start_bkd = 0
    except Exception as e:
        start_bkd = 0
        logger.error(e)

    stocks = [
        # "中成股份",
        # "通威股份",
        # "中天科技",
        # "均胜电子",
        # "长江电力",
        # "隆基绿能",
        # "华电科工",
        # "明阳智能",
        # "众鑫股份",
    ]
    cal_date = sh_date[start_bkd:start_bkd + cal_len]
    run_down(stocks, cal_date, N, cal_len)
